CREATE TABLE [dbo].[tb_FamiliaresACargo]
(
[idFamiliar] [int] NOT NULL IDENTITY(1, 1),
[idAgente] [int] NOT NULL,
[Parentezco] [int] NOT NULL,
[TipoDocumento] [int] NOT NULL,
[Documento] [int] NOT NULL,
[ApellidoYNombre] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Sexo] [int] NOT NULL,
[EstadoCivil] [int] NULL,
[FechaDeNacimiento] [datetime] NOT NULL,
[EsDiscapacitado] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CorrespondeAsignacionFamiliar] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ACargo] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NivelEducativoCompleto] [int] NULL,
[NivelActual] [int] NULL,
[CursoActual] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaCasamiento] [datetime] NULL,
[EstadoLaboral] [int] NULL,
[Vive] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[NivelCompleto] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTUser] [int] NULL CONSTRAINT [DF_tb_FamiliaresACargo_NTUser] DEFAULT ([dbo].[sp_usuario](suser_sname())),
[FechaCarga] [datetime] NULL CONSTRAINT [DF_tb_FamiliaresACargo_FechaCarga] DEFAULT (getdate())
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[tu_FamiliaresACargo] ON [dbo].[tb_FamiliaresACargo] 
FOR INSERT
AS
SET NOCOUNT ON
/*
DECLARE @Org INT
DECLARE @Age INT
DECLARE @NuevaOrganizacion INT

IF EXISTS(SELECT * FROM Inserted WHERE CorrespondeAsignacionFamiliar = 'S')
	AND NOT EXISTS(SELECT * FROM tb_ConceptosDelAgentePorOrganizacion INNER JOIN Inserted ON IdAgente = Agente AND Concepto = 12)
	AND EXISTS(SELECT * FROM tb_ServiciosAgentes S INNER JOIN tb_Organizaciones O ON UbicacionOrig = IdOrganizaciones INNER JOIN Inserted I ON I.idAgente = S.Agente WHERE S.FechaBaja IS NULL AND O.EsPrivada = 'S')
BEGIN
	SELECT @Age=Agente, @Org = Organizacion FROM  tb_ConceptosDelAgentePorOrganizacion INNER JOIN Inserted ON IdAgente = Agente AND Concepto = 33
             --IF @Age IS NOT NULL AND @Org IS NOT NULL
	    -- INSERT INTO tb_ConceptosDelAgentePorOrganizacion (Agente, Organizacion, Concepto)  VALUES (@Age, @Org, 12)
	--ELSE
             IF @Age IS NULL AND @Org IS  NULL
	BEGIN	
		--Se busca la nueva organizacion donde cobrar con la fecha mas antigua del servicio
		SELECT @NuevaOrganizacion = MIN(UbicacionOrig), @Age = MIN(S.Agente) FROM tb_ServiciosAgentes S
		INNER JOIN tb_Organizaciones O ON IdOrganizaciones = UbicacionOrig
		INNER JOIN tb_SubServiciosAgentes SS ON S.IdServicioAgente = SS.ServicioAgente
		INNER JOIN Inserted I ON I.idAgente = S.Agente
		WHERE EsPrivada = 'S'		
		AND SS.FechaBaja IS NULL
		AND S.FechaAlta = (SELECT MIN(FechaAlta) FROM tb_ServiciosAgentes S INNER JOIN tb_Organizaciones O ON UbicacionOrig = IdOrganizaciones 
				       WHERE EsPrivada = 'S' AND S.FechaBaja IS NULL AND S.Agente = I.IdAgente)

		-- Nueva Organizacion para cobrar SALARIO FAMILIAR Y ESTIMULOS
		--INSERT INTO tb_ConceptosDelAgentePorOrganizacion (Agente, Organizacion, Concepto) VALUES (@Age, @NuevaOrganizacion, 12)
		INSERT INTO tb_ConceptosDelAgentePorOrganizacion (Agente, Organizacion, Concepto) VALUES (@Age, @NuevaOrganizacion, 33)
	END
END

*/



GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] ADD CONSTRAINT [PK_tb_FamiliaresACargo] PRIMARY KEY NONCLUSTERED  ([idFamiliar]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] ADD CONSTRAINT [FK_tb_FamiliaresACargo_tb_EstadosCiviles] FOREIGN KEY ([EstadoCivil]) REFERENCES [dbo].[tb_EstadosCiviles] ([idEstadoCivil])
GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] ADD CONSTRAINT [FK_tb_FamiliaresACargo_tb_EstadosLaborales] FOREIGN KEY ([EstadoLaboral]) REFERENCES [dbo].[tb_EstadosLaborales] ([idEstadoLaboral])
GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] WITH NOCHECK ADD CONSTRAINT [FK_tb_FamiliaresACargo_tb_Agentes] FOREIGN KEY ([idAgente]) REFERENCES [dbo].[tb_Agentes] ([idAgente])
GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] WITH NOCHECK ADD CONSTRAINT [FK_tb_FamiliaresACargo_tb_NivelEstudios1] FOREIGN KEY ([NivelActual]) REFERENCES [dbo].[tb_NivelEstudios] ([idNivelEstudio])
GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] ADD CONSTRAINT [FK_tb_FamiliaresACargo_tb_NivelEstudios] FOREIGN KEY ([NivelEducativoCompleto]) REFERENCES [dbo].[tb_NivelEstudios] ([idNivelEstudio])
GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] ADD CONSTRAINT [FK_tb_FamiliaresACargo_tb_Parentezcos] FOREIGN KEY ([Parentezco]) REFERENCES [dbo].[tb_Parentezcos] ([idParentezco])
GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] WITH NOCHECK ADD CONSTRAINT [FK_tb_FamiliaresACargo_tb_Sexo] FOREIGN KEY ([Sexo]) REFERENCES [dbo].[tb_Sexo] ([idSexo])
GO
ALTER TABLE [dbo].[tb_FamiliaresACargo] WITH NOCHECK ADD CONSTRAINT [FK_tb_FamiliaresACargo_tb_TiposDeDocumento] FOREIGN KEY ([TipoDocumento]) REFERENCES [dbo].[tb_TiposDeDocumento] ([idTipoDocumento])
GO
